package com.quran.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bappi.utils.Constants;
import com.bappi.utils.Utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DatabaseAccessor {

	public DatabaseHelper myDbHelper = null;
	public SQLiteDatabase rdb = null;
	public SQLiteDatabase wdb = null;

	public DatabaseAccessor(Context context) throws Exception {
		try {
			myDbHelper = new DatabaseHelper(context);
			myDbHelper.openDataBase();
			rdb = myDbHelper.getReadableDatabase();
			wdb = myDbHelper.getWritableDatabase();
		} catch (Exception e) {
			Log.i("DREG", "DATABASE CAN NOT OPEN" + Utils.getException(e));
			myDbHelper.close();
			myDbHelper = null;
			throw e;
		}

	}

	public synchronized final void closeDB() {
		if (myDbHelper != null) {
			myDbHelper.close();
			rdb.close();
			wdb.close();
			myDbHelper = null;
			rdb = null;
			wdb = null;
		}
	}

	public final boolean doesExistsTable(String tableName) {
		boolean ret = false;
		try {
			String qry = "SELECT COUNT(*) FROM sqlite_master WHERE (type='table') AND (name='"
					+ tableName + "')ORDER BY name;";
			Cursor cursor = rdb.rawQuery(qry, null);
			while (cursor.moveToNext()) {
				if (Integer.parseInt(cursor.getString(0)) > 0) {
					ret = true;
				}
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public final void createTabeIfDoesNotExists(String tableName) {
		try {
			wdb.execSQL("create table if not exists " + tableName
					+ "(id INTEGER PRIMARY KEY, name TEXT)");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// ///////////////////************************************************///////////////////////////
	public List<String[]> getLanaguages() {
		List<String[]> ret = new ArrayList<String[]>();
		try {
			String[] columns = { "code", "title" };
			String tableName = "trans";
			Cursor cursor = rdb.query(tableName, columns, null, null, null,
					null, null);
			while (cursor.moveToNext()) {
				String[] row = new String[2];
				row[0] = cursor.getString(0);
				row[1] = cursor.getString(1);
				ret.add(row);
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public List<String[]> getSuras() {
		List<String[]> ret = new ArrayList<String[]>();
		try {
			String[] columns = { "id", "name_ara", "name_eng", "meaning",
					"verses_count", "is_makki" };
			String tableName = "sura";
			Cursor cursor = rdb.query(tableName, columns, null, null, null,
					null, null);
			while (cursor.moveToNext()) {
				String[] row = new String[6];
				row[0] = cursor.getString(0);
				row[1] = cursor.getString(1);
				row[2] = cursor.getString(2);
				row[3] = cursor.getString(3);
				row[4] = cursor.getString(4);
				row[5] = cursor.getString(5);
				ret.add(row);
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public List<String[]> getVerses(int suraId, String transCode) {
		List<String[]> ret = new ArrayList<String[]>();
		try {

			if (Constants.LANG_CODE_NONE.equals(transCode)) {
				String qry = "SELECT verses.id,verses.modified_content,verses.special_chars FROM verses "
						+ " WHERE (sura_id=%d) ORDER BY id";
				qry = String.format(qry, suraId);
				Log.i("DREG", "QRY1=" + qry);
				Cursor cursor = rdb.rawQuery(qry, null);
				while (cursor.moveToNext()) {

					String[] row = new String[3];
					row[0] = cursor.getString(0);
					row[1] = cursor.getString(1);
					row[2] = cursor.getString(2);
					ret.add(row);
				}
				cursor.close();
			} else {
				String qry = "SELECT verses.id,verses.modified_content,verses.special_chars,%s.content FROM verses,%s "
						+ " WHERE ((id=verses_id) AND (sura_id=%d)) ORDER BY id";
				qry = String.format(qry, transCode, transCode, suraId);
				Log.i("DREG", "QRY2=" + qry);
				Cursor cursor = rdb.rawQuery(qry, null);
				while (cursor.moveToNext()) {
					String[] row = new String[4];
					row[0] = cursor.getString(0);
					row[1] = cursor.getString(1);
					row[2] = cursor.getString(2);
					row[3] = cursor.getString(3);
					ret.add(row);
				}
				cursor.close();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public List<String[]> getBookmarkVerses(String transCode) {
		List<String[]> ret = new ArrayList<String[]>();
		try {
			if (Constants.LANG_CODE_NONE.equals(transCode)) {
				String qry = "SELECT verses.id,verses.modified_content,verses.special_chars,"
						+ " bookmark.sura_id, bookmark.sura_ar,bookmark.sura_en,bookmark.meaning,"
						+ " bookmark.verse_no FROM verses,bookmark  WHERE (verses.id = bookmark.verses_id) ORDER BY  bookmark.time desc";

				Cursor cursor = rdb.rawQuery(qry, null);
				while (cursor.moveToNext()) {

					String[] row = new String[9];
					row[0] = cursor.getString(0);
					row[1] = cursor.getString(1);
					row[2] = cursor.getString(2);
					row[4] = cursor.getString(3);
					row[5] = cursor.getString(4);
					row[6] = cursor.getString(5);
					row[7] = cursor.getString(6);
					row[8] = cursor.getString(7);
					ret.add(row);
				}
				cursor.close();
			} else {
				String qry = "SELECT verses.id,verses.modified_content,verses.special_chars,%s.content,bookmark.sura_id,"
						+ " bookmark.sura_ar,bookmark.sura_en,bookmark.meaning,bookmark.verse_no FROM verses,%s,bookmark "
						+ " WHERE ((verses.id=%s.verses_id) AND (verses.id = bookmark.verses_id)) ORDER BY  bookmark.time desc";
				qry = String.format(qry, transCode, transCode, transCode);
				Cursor cursor = rdb.rawQuery(qry, null);
				while (cursor.moveToNext()) {

					String[] row = new String[9];
					row[0] = cursor.getString(0);
					row[1] = cursor.getString(1);
					row[2] = cursor.getString(2);
					row[3] = cursor.getString(3);
					row[4] = cursor.getString(4);
					row[5] = cursor.getString(5);
					row[6] = cursor.getString(6);
					row[7] = cursor.getString(7);
					row[8] = cursor.getString(8);
					ret.add(row);
				}
				cursor.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public List<String> searchWords(String tableName, String prefix,
			int maxCount) {
		List<String> ret = new ArrayList<String>();
		try {
			String[] selections = null;
			prefix = prefix.replace("'", "''");
			String qry = String
					.format("SELECT word FROM %s WHERE word like '%s' ORDER BY word ASC LIMIT %d ",
							tableName, (prefix + "%"), maxCount);
			Cursor cursor = rdb.rawQuery(qry, selections);
			while (cursor.moveToNext()) {
				ret.add(cursor.getString(0));
			}
			cursor.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public List<String[]> searchVerses(String transCode, String searchKey,
			int max) {
		List<String[]> ret = new ArrayList<String[]>();
		try {
			// String searchKeyAra = "%" + searchKey+ "%";
			searchKey = "%" + searchKey + "%";
			String ids = null;
			String qry = null;
			Cursor cursor = null;
			Map<String, String[]> map = new HashMap<String, String[]>();
			if (Constants.LANG_CODE_NONE.equals(transCode)) {
				qry = "SELECT verses.id,verses.modified_content,verses.special_chars "
						+ " FROM verses  WHERE  (verses.id IN ( SELECT id FROM  ( SELECT distinct id "
						+ " from verses WHERE modified_content like '%s') ORDER BY RANDOM() LIMIT %d ))";
				qry = String.format(qry, searchKey, max);
				Log.i("DREG", "QRY=" + qry);
				cursor = rdb.rawQuery(qry, null);
				while (cursor.moveToNext()) {

					String[] row = new String[9];
					row[0] = cursor.getString(0);
					row[1] = cursor.getString(1);
					row[2] = cursor.getString(2);
					if (ids == null) {
						ids = row[0];
					} else {
						ids += ",".concat(row[0]);
					}
					map.put(row[0], row);
				}
				cursor.close();

			} else {

				qry = "SELECT verses.id,verses.modified_content,verses.special_chars,"
						+ " %s.content FROM verses,%s  WHERE  ((id=verses_id) AND "
						+ " (verses_id IN ( SELECT id FROM  ( SELECT distinct verses_id as id FROM "
						+ " %s WHERE content like '%s' union  SELECT id  from verses WHERE "
						+ " modified_content like '%s') ORDER BY RANDOM() LIMIT %d )))";
				qry = String.format(qry, transCode, transCode, transCode,
						searchKey, searchKey, max);
				Log.i("DREG", "QRY=" + qry);
				cursor = rdb.rawQuery(qry, null);

				while (cursor.moveToNext()) {

					String[] row = new String[9];
					row[0] = cursor.getString(0);
					row[1] = cursor.getString(1);
					row[2] = cursor.getString(2);
					row[3] = cursor.getString(3);
					if (ids == null) {
						ids = row[0];
					} else {
						ids += ",".concat(row[0]);
					}
					map.put(row[0], row);
				}
				cursor.close();
			}

			if (ids != null) {

				qry = String
						.format("SELECT verses.id,sura.id,name_ara,name_eng,meaning,verses_start "
								+ "FROM sura,verses WHERE (verses.id IN (%s) AND (verses.sura_id = sura.id))",
								ids);
				cursor = rdb.rawQuery(qry, null);
				String id;
				while (cursor.moveToNext()) {
					id = cursor.getString(0);
					String[] row = map.get(id);
					if (row != null) {
						row[4] = cursor.getString(1);
						row[5] = cursor.getString(2);
						row[6] = cursor.getString(3);
						row[7] = cursor.getString(4);
						row[8] = Integer.toString((Integer.parseInt(row[0])
								- Integer.parseInt(cursor.getString(5)) + 1));
						ret.add(row);
					}
				}
				cursor.close();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public boolean addToBookmark(int versesId, int suraId, int verseNo,
			String nameAr, String nameEn, String meaning) {
		boolean ret = false;
		try {
			String tableName = "bookmark";
			ContentValues values = new ContentValues();
			values.put("verses_id", versesId);
			values.put("sura_id", suraId);
			values.put("sura_ar", nameAr);
			values.put("sura_en", nameEn);
			values.put("meaning", meaning);
			values.put("verse_no", verseNo);
			values.put("time", System.currentTimeMillis());
			wdb.insertOrThrow(tableName, null, values);
			ret = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public void deleteFromBookmark(int versesId) {
		try {
			String tableName = "bookmark";
			wdb.delete(tableName, " verses_id = " + versesId, null);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// /////////////////////************************************************///////////////////////////
	public void insertData(String tableName, ContentValues values) {
		try {
			wdb.insertOrThrow(tableName, null, values);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// /////////////////////************************************************///////////////////////////
}
